# -*- coding: utf-8 -*-

from PySide6.QtCore import QDate, Qt
from PySide6.QtWidgets import QMainWindow, QApplication, QFileDialog, QWidget, QTableWidgetItem, QMessageBox

import logging
import sys

from mainWindow import Ui_MainWindow
from askForLeave import Ui_Form_askForLeave
from weekend import Ui_Form_weekend

from win32com import client
from datetime import date, time, datetime

global staffs
global start_date
global end_date
global g_weekends
global time2index
global index2time

staffs = {}
start_date = datetime.now()
end_date = date(start_date.year, start_date.month, 1)
g_weekends = {1: {1: False, 2: False, 3: False, 4: False},
              2: {1: False, 2: False, 3: False, 4: False},
              3: {1: False, 2: False, 3: False, 4: False},
              4: {1: False, 2: False, 3: False, 4: False},
              5: {1: False, 2: False, 3: False, 4: False},
              6: {1: False, 2: False, 3: False, 4: False},
              7: {1: False, 2: False, 3: False, 4: False},
              8: {1: False, 2: False, 3: False, 4: False},
              9: {1: False, 2: False, 3: False, 4: False},
              10: {1: False, 2: False, 3: False, 4: False},
              11: {1: False, 2: False, 3: False, 4: False},
              12: {1: False, 2: False, 3: False, 4: False},
              13: {1: False, 2: False, 3: False, 4: False},
              14: {1: False, 2: False, 3: False, 4: False},
              15: {1: False, 2: False, 3: False, 4: False},
              16: {1: False, 2: False, 3: False, 4: False},
              17: {1: False, 2: False, 3: False, 4: False},
              18: {1: False, 2: False, 3: False, 4: False},
              19: {1: False, 2: False, 3: False, 4: False},
              20: {1: False, 2: False, 3: False, 4: False},
              21: {1: False, 2: False, 3: False, 4: False},
              22: {1: False, 2: False, 3: False, 4: False},
              23: {1: False, 2: False, 3: False, 4: False},
              24: {1: False, 2: False, 3: False, 4: False},
              25: {1: False, 2: False, 3: False, 4: False},
              26: {1: False, 2: False, 3: False, 4: False},
              27: {1: False, 2: False, 3: False, 4: False},
              28: {1: False, 2: False, 3: False, 4: False},
              29: {1: False, 2: False, 3: False, 4: False},
              30: {1: False, 2: False, 3: False, 4: False},
              31: {1: False, 2: False, 3: False, 4: False},
              }
time2index = {'上午上班': 1, '上午下班': 2, '下午上班': 3, '下午下班': 4, '上午': 5, '下午': 6, '全天': 7}
index2time = {1: '上午上班', 2: '上午下班', 3: '下午上班', 4: '下午下班', 5: '上午', 6: '下午', 7: '全天'}

tool_version = '1.4'


class Weekend(QWidget):
    def __init__(self):
        global staffs
        super(Weekend, self).__init__()

        self.weekend_window = Ui_Form_weekend()
        self.weekend_window.setupUi(self)

        self.weekend_window.pushButton_add.clicked.connect(self.__add_cb)
        self.weekend_window.pushButton_delete.clicked.connect(self.__delete_cb)
        self.weekend_window.calendarWidget.clicked.connect(self.__select_day_cb)

        self.__curr_day = 0

        self.weekend_window.calendarWidget.setCurrentPage(start_date.year, start_date.month)
        self.weekend_window.calendarWidget.setMinimumDate(QDate(start_date.year, start_date.month, start_date.day))
        self.weekend_window.calendarWidget.setMaximumDate(QDate(end_date.year, end_date.month, end_date.day))

        self.__display_update()

    def __select_day_cb(self, qdata):
        self.__curr_day = int(qdata.day())

    def __add_cb(self):
        global g_weekends
        tim = self.weekend_window.comboBox_time.currentText()
        if self.__curr_day != 0:
            tim_idx = time2index[tim]
            if tim_idx == 1 or tim_idx == 2 or tim_idx == 3 or tim_idx == 4:
                g_weekends[self.__curr_day][tim_idx] = True
            if tim_idx == 5:
                g_weekends[self.__curr_day][1] = True
                g_weekends[self.__curr_day][2] = True
            if tim_idx == 6:
                g_weekends[self.__curr_day][3] = True
                g_weekends[self.__curr_day][4] = True
            if tim_idx == 7:
                g_weekends[self.__curr_day][1] = True
                g_weekends[self.__curr_day][2] = True
                g_weekends[self.__curr_day][3] = True
                g_weekends[self.__curr_day][4] = True

        self.__display_update()

    def __display_update(self):
        global index2time
        self.weekend_window.tableWidget.setRowCount(31 * 4)
        self.weekend_window.tableWidget.clearContents()
        line = 0
        for day in g_weekends:
            for wkd in g_weekends[day]:
                if g_weekends[day][wkd] is True:
                    itm_day = QTableWidgetItem(str(day))
                    itm_wkd = QTableWidgetItem(index2time[wkd])
                    self.weekend_window.tableWidget.setItem(line, 0, itm_day)
                    self.weekend_window.tableWidget.setItem(line, 1, itm_wkd)
                    line += 1

    def __delete_cb(self):
        global g_weekends

        curr_row = self.weekend_window.tableWidget.currentRow()
        itm_day = self.weekend_window.tableWidget.item(curr_row, 0)
        itm_wkd = self.weekend_window.tableWidget.item(curr_row, 1)
        g_weekends[int(itm_day.text())][time2index[itm_wkd.text()]] = False

        self.__display_update()


class AskForLeave(QWidget):
    def __init__(self):
        global staffs
        global start_date
        super(AskForLeave, self).__init__()
        self.__current_day = 0
        self.ask_for_leave_window = Ui_Form_askForLeave()
        self.ask_for_leave_window.setupUi(self)

        # 姓名
        self.ask_for_leave_window.comboBox_name.addItems(staffs.keys())

        # 日期
        self.ask_for_leave_window.calendarWidget.clicked.connect(self.__select_day_cb)

        self.ask_for_leave_window.pushButton_add.clicked.connect(self.__add_cb)
        self.ask_for_leave_window.pushButton_delete.clicked.connect(self.__delete_cb)

        self.ask_for_leave_window.calendarWidget.setCurrentPage(start_date.year, start_date.month)
        self.ask_for_leave_window.calendarWidget.setMaximumDate(QDate(end_date.year, end_date.month, end_date.day))
        self.ask_for_leave_window.calendarWidget.setMinimumDate(QDate(start_date.year, start_date.month, start_date.day))
        self.__display_update()

    def __select_day_cb(self, date):
        self.__current_day = date.day()

    def __add_cb(self):
        global staffs
        global time2index

        name = self.ask_for_leave_window.comboBox_name.currentText()
        day = self.__current_day
        tim = self.ask_for_leave_window.comboBox_time.currentText()
        tim_idx = time2index[tim]
        if tim_idx == 1 or tim_idx == 2 or tim_idx == 3 or tim_idx == 4:
            staffs[name].work_days[day].askForLeave[tim_idx] = True
        elif tim_idx == 5:
            staffs[name].work_days[day].askForLeave[1] = True
            staffs[name].work_days[day].askForLeave[2] = True
        elif tim_idx == 6:
            staffs[name].work_days[day].askForLeave[3] = True
            staffs[name].work_days[day].askForLeave[4] = True
        else:
            staffs[name].work_days[day].askForLeave[1] = True
            staffs[name].work_days[day].askForLeave[2] = True
            staffs[name].work_days[day].askForLeave[3] = True
            staffs[name].work_days[day].askForLeave[4] = True

        self.__display_update()

    def __delete_cb(self):
        global staffs
        global time2index
        curr_row = self.ask_for_leave_window.tableWidget_askForLeaveTable.currentRow()
        itm_name = self.ask_for_leave_window.tableWidget_askForLeaveTable.item(curr_row, 0)
        itm_day = self.ask_for_leave_window.tableWidget_askForLeaveTable.item(curr_row, 1)
        itm_tim = self.ask_for_leave_window.tableWidget_askForLeaveTable.item(curr_row, 2)
        tim_idx = time2index[itm_tim.text()]
        staffs[itm_name.text()].work_days[int(itm_day.text())].askForLeave[tim_idx] = False

        self.__display_update()

    def __display_update(self):
        global index2time
        global staffs

        # 最长显示每个员工请过一次假
        cnt = len(staffs)

        self.ask_for_leave_window.tableWidget_askForLeaveTable.setRowCount(cnt)
        self.ask_for_leave_window.tableWidget_askForLeaveTable.clearContents()
        line = 0
        for name, staff in staffs.items():
            wds = staff.work_days
            for day, wd in wds.items():
                _, num = wd.get_ask_for_leave()
                if num > 0:
                    for i in range(1, 5, 1):
                        if wd.askForLeave[i]:
                            itm_name = QTableWidgetItem(name)
                            itm_date = QTableWidgetItem(str(day))
                            itm_tim = QTableWidgetItem(index2time[i])
                            self.ask_for_leave_window.tableWidget_askForLeaveTable.setItem(line, 0, itm_name)
                            self.ask_for_leave_window.tableWidget_askForLeaveTable.setItem(line, 1, itm_date)
                            self.ask_for_leave_window.tableWidget_askForLeaveTable.setItem(line, 2, itm_tim)
                            line = line + 1


class WorkDay:
    def __init__(self, day, raw):
        self.day = day
        self.raw = raw
        self.att_raw = {1: '', 2: '', 3: '', 4: ''}
        self.late = {1: False, 2: False, 3: False, 4: False}
        self.askForLeave = {1: False, 2: False, 3: False, 4: False}

        self.__get_att()

    def __get_att(self):
        if self.raw == '':
            return
        att_raw = self.raw.split('\n')
        att = list(set(att_raw))
        att.sort(key=att_raw.index)
        first = time(8, 0)
        second = time(12, 5)
        third = time(13, 10)
        forth = time(14, 30)
        fifth = time(17, 5)
        for line in att:
            if ':' in line:
                tmp = line.strip().split(':')
            else:
                continue
            t = time(int(tmp[0]), int(tmp[1]))  # 打卡时间
            if t.__le__(first):
                if self.att_raw[1] == '':
                    self.att_raw[1] = line.strip()
            if t.__gt__(first) and t.__lt__(second):
                if self.att_raw[1] == '':
                    self.late[1] = True
                    self.att_raw[1] = line.strip()
            if t.__ge__(second) and t.__le__(third):
                if self.att_raw[2] == '':
                    self.att_raw[2] = line.strip()
            if t.__ge__(third) and t.__le__(forth):
                if self.att_raw[2] == '13:10' and t.__eq__(third):
                    pass
                else:
                    if self.att_raw[3] == '':
                        self.att_raw[3] = line.strip()
            if t.__gt__(forth) and t.__lt__(fifth):
                if self.att_raw[3] == '':
                    self.late[3] = True
                    self.att_raw[3] = line.strip()
            if t.__ge__(fifth):
                self.att_raw[4] = line.strip()

    def get_late(self):
        cnt = 0
        late = []
        for i in range(1, 5, 1):
            if self.late[i]:
                cnt += 1
                late.append(i)

        return late, cnt

    def get_ask_for_leave(self):
        cnt = 0
        ask_for_leave = []
        for i in range(1, 5, 1):
            if self.askForLeave[i]:
                cnt += 1
                ask_for_leave.append(i)

        return ask_for_leave, cnt

    def get_loss(self):
        global g_weekends
        cnt = 0
        loss = []
        for i in range(1, 5, 1):
            if self.att_raw[i] == '' and not g_weekends[self.day][i]:
                cnt += 1
                loss.append(i)

        return loss, cnt

    def get_att_string(self):
        global g_weekends
        att = {1: '', 2: '', 3: '', 4: ''}
        att_string = ''
        for i in range(1, 5, 1):
            if g_weekends[self.day][i]:
                return att_string
            else:
                if self.askForLeave[i]:
                    att[i] = '请假\n'
                else:
                    if self.late[i]:
                        att[i] = '迟到\n'
                    else:
                        if self.att_raw[i] == '':
                            att[i] = '缺卡\n'
                        else:
                            att[i] = '\n'

        for i in range(1, 5, 1):
            att_string += att[i]

        return att_string

    def get_stat(self):
        global g_weekends

        late = 0
        loss = 0
        leave = 0
        for i in range(1, 5, 1):
            if not self.askForLeave[i] and not g_weekends[self.day][i]:
                if self.late[i]:
                    late += 1
                if self.att_raw[i] == '':
                    loss += 1
            if self.askForLeave[i]:
                leave += 1

        return late, loss, leave

    def get_normal(self):
        global g_weekends
        cnt = 0
        normal = []
        for i in range(1, 5, 1):
            if self.late[i] is False \
                    and self.askForLeave[i] is False \
                    and self.att_raw != "" \
                    and g_weekends[self.day][i] is False:
                normal.append(self.att_raw[i])

        return normal


class Staff:
    def __init__(self, name):
        self.name = name
        self.work_days = {}

    def get_late_day(self):
        late_days = []
        cnt = 0
        for day, work in self.work_days.items():
            _, late_num = work.get_late()
            if late_num != 0:
                cnt += 1
                late_days.append(day)

        return late_days, cnt

    def get_loss_day(self):
        loss_days = []
        cnt = 0
        for day, wd in self.work_days.items():
            _, loss_num = wd.get_loss()
            if loss_num != 0:
                cnt += 1
                loss_days.append(day)

        return loss_days, cnt


class Tool(QMainWindow):
    def __init__(self):
        super(Tool, self).__init__()

        self.main_window = Ui_MainWindow()
        self.main_window.setupUi(self)

        self.setWindowTitle('考勤工具_v_' + tool_version)

        # 文件
        self.main_window.action_import_att.triggered.connect(self.__import_att_cb)
        self.main_window.action_export_leave.triggered.connect(self.__export_leave_cb)

        self.main_window.listWidget_name.itemClicked.connect(self.__select_staff_cb)
        self.main_window.calendarWidget.clicked.connect(self.__select_day_cb)

        self.main_window.action_stat.triggered.connect(self.__export_stat_cb)

        # 窗口
        self.main_window.action_ask_for_leave.triggered.connect(self.__ask_for_leave_window_cb)
        self.main_window.action_weekend.triggered.connect(self.__weekend_window_cb)

        # 搜索
        self.main_window.pushButton_search.clicked.connect(self.__search_cb)

        self.__curr_staff = ''
        self.__curr_day = 0

    def __search_cb(self):
        name = self.main_window.lineEdit_staff.text()
        match = self.main_window.listWidget_name.findItems(name, Qt.MatchContains)
        if len(match) != 0:
            self.main_window.listWidget_name.setCurrentItem(match[0])
        else:
            msg = '未找到 ' + name
            QMessageBox.information(self, '搜索', msg)

    def __export_leave_cb(self):
        global start_date
        global end_date

        workbook = self.et.Workbooks.Add()
        workbook.Worksheets.Item(1).Name = '请假'
        leave_sheet = workbook.Worksheets['请假']

        work_day_number = end_date.day - start_date.day + 1
        ord_b = ord('B')
        ord_a = ord('A')
        ord_z = ord('Z')
        for j in range(work_day_number):
            day = start_date.day + j
            if (ord_b + j) > ord_z:
                work_day_cell_row = 'A' + chr((ord_b + j - ord_z) + ord_a - 1)
            else:
                work_day_cell_row = chr(ord_b + j)
            work_day_cell = work_day_cell_row + '1'
            leave_sheet.Range(work_day_cell).Value2 = str(day)

        counter = 2
        for name, staff in staffs.items():
            name_cell = 'A' + str(counter)
            leave_sheet.Range(name_cell).Value2 = name

            for j in range(work_day_number):
                if (ord_b + j) > ord('Z'):
                    work_day_cell_row = 'A' + chr((ord_b + j - ord_z) + ord_a - 1)
                else:
                    work_day_cell_row = chr(ord_b + j)
                work_day_cell = work_day_cell_row + str(counter)
                leave, _ = staff.work_days[j + start_date.day].get_ask_for_leave()
                leave_sheet.Range(work_day_cell).Value2 = str(leave).strip('[]')

                if len(leave) != 0:
                    # loss_sheet.Range(work_day_cell).Interior.Color = 0x00FFFF
                    leave_sheet.Range(work_day_cell).Font.Color = 0x0000FF
                    leave_sheet.Range(work_day_cell).Font.Bold = True
                else:
                    # loss_sheet.Range(work_day_cell).Interior.Color = 0xFFFFFF
                    leave_sheet.Range(work_day_cell).Font.Color = 0x000000
                    leave_sheet.Range(work_day_cell).Font.Bold = False

            counter += 1
        workbook.Close()

        QMessageBox.information(self, '导出请假表', '完成')

    def __import_att_cb(self):
        global start_date
        global end_date

        fname, _ = QFileDialog.getOpenFileName(self)
        if fname == '':
            return

        self.et = client.Dispatch('Ket.Application')
        self.et.Visible = True
        self.workbook = self.et.Workbooks.Open(fname)
        att_sheet = self.workbook.Worksheets('打卡时间')
        self.workbook.Worksheets.Item('打卡时间').Activate()

        # 读取时间
        tmp = att_sheet.Range('A1').Value2.split('：')[1].split('至')
        start_date = date.fromisoformat(tmp[0].strip())
        end_date = date.fromisoformat(tmp[1].strip())
        work_day_number = end_date.day - start_date.day + 1

        ord_g = ord('G')
        ord_z = ord('Z')
        ord_a = ord('A')
        for i in range(4, 1000):
            # 姓名
            name_cell = 'A' + str(i)
            name = att_sheet.Range(name_cell).Value2
            if name is None:
                break
            staff = Staff(name)

            # 打卡
            for j in range(work_day_number):
                day = start_date.day + j
                if (ord_g + j) > ord_z:
                    work_day_cell_row = 'A' + chr((ord_g + j - ord_z) + ord_a - 1)
                else:
                    work_day_cell_row = chr(ord_g + j)
                work_day_cell = work_day_cell_row + str(i)
                work_day_raw = att_sheet.Range(work_day_cell).Value2
                if work_day_raw is not None:
                    staff.work_days[day] = WorkDay(day, work_day_raw)
                else:
                    staff.work_days[day] = WorkDay(day, '')

            staffs[name] = staff

        # 初始化显示
        staff_names = []
        for name, staff in staffs.items():
            staff_names.append(name)

        self.main_window.listWidget_name.addItems(staff_names)
        self.main_window.calendarWidget.setCurrentPage(start_date.year, start_date.month)
        self.main_window.calendarWidget.setMinimumDate(QDate(start_date.year, start_date.month, start_date.day))
        self.main_window.calendarWidget.setMaximumDate(QDate(end_date.year, end_date.month, end_date.day))

        QMessageBox.information(self, '导入考勤表', '完成')

    def __select_day_cb(self, qdate):
        if (qdate.year() == start_date.year) or (qdate.month() == start_date.month):
            self.__curr_day = qdate.day()
            self.__display()
            self.__show_stat()

    def __select_staff_cb(self, item):
        self.__curr_staff = item.text()
        logging.info('current staff ' + self.__curr_staff)
        self.__display()
        self.__show_stat()

    def __show_stat(self):
        global staffs
        if self.__curr_staff != '':
            late_days, late_num = staffs[self.__curr_staff].get_late_day()
            loss_days, loss_num = staffs[self.__curr_staff].get_loss_day()

            string = '迟到 ' + str(late_num) + ' 天 ： ' + str(late_days) + '  缺卡 ' + str(loss_num) + ' 天 ： ' + str(
                loss_days)
            self.main_window.lineEdit_stat.setText(string)

    def __display(self):
        global staffs
        global g_weekends
        if self.__curr_day != 0 and self.__curr_staff != '':
            staff = staffs[self.__curr_staff]
            work_day = staff.work_days[self.__curr_day]
            if not g_weekends[work_day.day][1]:
                t = work_day.att_raw[1]
                self.main_window.lineEdit_leave_0.setText(t)
            else:
                self.main_window.lineEdit_leave_0.setText('休息')

            if not g_weekends[work_day.day][2]:
                t = work_day.att_raw[2]
                self.main_window.lineEdit_leave_1.setText(t)
            else:
                self.main_window.lineEdit_leave_1.setText('休息')

            if not g_weekends[work_day.day][3]:
                t = work_day.att_raw[3]
                self.main_window.lineEdit_leave_2.setText(t)
            else:
                self.main_window.lineEdit_leave_2.setText('休息')

            if not g_weekends[work_day.day][4]:
                t = work_day.att_raw[4]
                self.main_window.lineEdit_leave_3.setText(t)
            else:
                self.main_window.lineEdit_leave_3.setText('休息')

            # 请假
            leave, _ = staffs[self.__curr_staff].work_days[self.__curr_day].get_ask_for_leave()
            if 1 in leave:
                self.main_window.checkBox_leave_0.setChecked(True)
            else:
                self.main_window.checkBox_leave_0.setChecked(False)

            if 2 in leave:
                self.main_window.checkBox_leave_1.setChecked(True)
            else:
                self.main_window.checkBox_leave_1.setChecked(False)

            if 3 in leave:
                self.main_window.checkBox_leave_2.setChecked(True)
            else:
                self.main_window.checkBox_leave_2.setChecked(False)

            if 4 in leave:
                self.main_window.checkBox_leave_3.setChecked(True)
            else:
                self.main_window.checkBox_leave_3.setChecked(False)

            _, num = staffs[self.__curr_staff].work_days[self.__curr_day].get_loss()
            _, late_num = staffs[self.__curr_staff].work_days[self.__curr_day].get_late()
            string = '缺卡 ： ' + str(num) + ' 次\n' + '迟到 : ' + str(late_num) + '次\n'
            self.main_window.textEdit_raw.setText(string)

    def __ask_for_leave_window_cb(self):
        self.ask_for_leave_window = AskForLeave()
        self.ask_for_leave_window.show()

    def __weekend_window_cb(self):
        self.weekend_window = Weekend()
        self.weekend_window.show()

    def __export_stat_cb(self):
        global end_date
        global start_date

        sheets_name = []
        for sheet in self.workbook.Worksheets:
            sheets_name.append(sheet.Name)

        # 明细
        if '明细' not in sheets_name:
            self.workbook.Worksheets.Add()
            self.workbook.Worksheets.Item(1).Name = '明细'
        self.workbook.Worksheets['明细'].Activate()
        loss_sheet = self.workbook.Worksheets['明细']
        work_day_number = end_date.day - start_date.day + 1

        ord_b = ord('B')
        ord_z = ord('Z')
        ord_a = ord('A')
        for j in range(work_day_number):
            day = start_date.day + j
            if (ord_b + j) > ord_z:
                work_day_cell_row = 'A' + chr((ord_b + j - ord_z) + ord_a - 1)
            else:
                work_day_cell_row = chr(ord_b + j)
            work_day_cell = work_day_cell_row + '1'
            loss_sheet.Range(work_day_cell).Value2 = str(day)

        counter = 2
        for name, staff in staffs.items():
            name_cell = 'A' + str(counter)
            loss_sheet.Range(name_cell).Value2 = name

            for j in range(work_day_number):
                if (ord_b + j) > ord_z:
                    work_day_cell_row = 'A' + chr((ord_b + j - ord_z) + ord_a - 1)
                else:
                    work_day_cell_row = chr(ord_b + j)
                work_day_cell = work_day_cell_row + str(counter)
                att_string = staff.work_days[j + start_date.day].get_att_string()
                loss_sheet.Range(work_day_cell).Value2 = att_string

                # if num != 0:
                #     # loss_sheet.Range(work_day_cell).Interior.Color = 0x00FFFF
                #     loss_sheet.Range(work_day_cell).Font.Color = 0x0000FF
                #     loss_sheet.Range(work_day_cell).Font.Bold = True
                # else:
                #     # loss_sheet.Range(work_day_cell).Interior.Color = 0xFFFFFF
                #     loss_sheet.Range(work_day_cell).Font.Color = 0x000000
                #     loss_sheet.Range(work_day_cell).Font.Bold = False

            counter += 1

        # 汇总
        if '汇总' not in sheets_name:
            self.workbook.Worksheets.Add()
            self.workbook.Worksheets.Item(1).Name = '汇总'
        self.workbook.Worksheets['汇总'].Activate()
        late_sheet = self.workbook.Sheets['汇总']
        work_day_number = end_date.day - start_date.day + 1

        for j in range(work_day_number):
            day = start_date.day + j
            if (ord_b + j) > ord_z:
                work_day_cell_row = 'A' + chr((ord_b + j - ord_z) + ord_a - 1)
            else:
                work_day_cell_row = chr(ord_b + j)
            work_day_cell = work_day_cell_row + '1'
            late_sheet.Range(work_day_cell).Value2 = str(day)

        counter = 2
        for name, staff in staffs.items():
            name_cell = 'A' + str(counter)
            late_sheet.Range(name_cell).Value2 = name

            for j in range(work_day_number):
                if (ord_b + j) > ord_z:
                    work_day_cell_row = 'A' + chr((ord_b + j - ord_z) + ord_a - 1)
                else:
                    work_day_cell_row = chr(ord_b + j)
                work_day_cell = work_day_cell_row + str(counter)
                late, loss, leave = staff.work_days[j + start_date.day].get_stat()
                tmp = ''
                if late != 0:
                    tmp = '迟' + str(late) + '次\n'
                if loss != 0:
                    tmp += '缺' + str(loss) + '次\n'
                if leave != 0:
                    tmp += '请假'
                late_sheet.Range(work_day_cell).Value2 = tmp

                # if num != 0:
                #     # late_sheet.Range(work_day_cell).Interior.Color = 0x00FFFF
                #     late_sheet.Range(work_day_cell).Font.Color = 0x0000FF
                #     late_sheet.Range(work_day_cell).Font.Bold = True
                # else:
                #     # late_sheet.Range(work_day_cell).Interior.Color = 0xFFFFFF
                #     late_sheet.Range(work_day_cell).Font.Color = 0x000000
                #     late_sheet.Range(work_day_cell).Font.Bold = False

            counter += 1

        # 汇总2
        if '汇总2' not in sheets_name:
            self.workbook.Worksheets.Add()
            self.workbook.Worksheets.Item(1).Name = '汇总2'
        self.workbook.Worksheets['汇总2'].Activate()
        late_sheet = self.workbook.Sheets['汇总2']
        work_day_number = end_date.day - start_date.day + 1

        for j in range(work_day_number):
            day = start_date.day + j
            if (ord_b + j) > ord_z:
                work_day_cell_row = 'A' + chr((ord_b + j - ord_z) + ord_a - 1)
            else:
                work_day_cell_row = chr(ord_b + j)
            work_day_cell = work_day_cell_row + '1'
            late_sheet.Range(work_day_cell).Value2 = str(day)

        counter = 2
        for name, staff in staffs.items():
            name_cell = 'A' + str(counter)
            late_sheet.Range(name_cell).Value2 = name

            for j in range(work_day_number):
                if (ord_b + j) > ord_z:
                    work_day_cell_row = 'A' + chr((ord_b + j - ord_z) + ord_a - 1)
                else:
                    work_day_cell_row = chr(ord_b + j)
                work_day_cell = work_day_cell_row + str(counter)
                late, loss, leave = staff.work_days[j + start_date.day].get_stat()
                tmp = ''
                if late != 0:
                    tmp = '迟' + str(late) + '次\n'
                if loss != 0:
                    tmp += '缺' + str(loss) + '次\n'
                if leave != 0:
                    tmp += '请假\n'

                normal = staff.work_days[j + start_date.day].get_normal()
                for n in normal:
                    tmp += n + '\n'
                tmp = "\n".join(tmp.split())
                late_sheet.Range(work_day_cell).Value2 = tmp

                # if num != 0:
                #     # late_sheet.Range(work_day_cell).Interior.Color = 0x00FFFF
                #     late_sheet.Range(work_day_cell).Font.Color = 0x0000FF
                #     late_sheet.Range(work_day_cell).Font.Bold = True
                # else:
                #     # late_sheet.Range(work_day_cell).Interior.Color = 0xFFFFFF
                #     late_sheet.Range(work_day_cell).Font.Color = 0x000000
                #     late_sheet.Range(work_day_cell).Font.Bold = False

            counter += 1

        self.workbook.Save()

        QMessageBox.information(self, '导出考勤表', '完成')

    def closeEvent(self, event):
        self.workbook.Close()
        self.et.Quit()
        del self.workbook
        del self.et
        return super().closeEvent(event)


if __name__ == '__main__':
    logging.basicConfig(level=logging.DEBUG)

    app = QApplication(sys.argv)
    tool = Tool()
    tool.show()
    sys.exit(app.exec())
